//
//  DataBaseHandle.m
//  UI20_数据库练习
//
//  Created by dllo on 16/1/5.
//  Copyright © 2016年 yy. All rights reserved.
//

#import "DataBaseHandle.h"

@implementation DataBaseHandle

+ (instancetype)shareDB{
    static DataBaseHandle *dataBase = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataBase = [[DataBaseHandle alloc]init];
        [dataBase openDB];
        [dataBase createTable];
    });
    return dataBase;
}

- (void)openDB{
    NSString *sandBoxPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, 1, YES)lastObject];
    NSLog(@"%@",sandBoxPath);
    NSString *docPath = [sandBoxPath stringByAppendingPathComponent:@"Collect.sqlite"];
    int result = sqlite3_open([docPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"成功");
    }else{
        NSLog(@"失败");
    }
}

- (void)createTable{
//    NSString *sqlStr = @"create table if not exists activity(activityID text,title text)";
    NSString *sqlStr = @"create table if not exists collect(photo_id text,pic text,col_name text,pic_detail text,col_count text,comment_count text,flag integer)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建成功");
    }else{
        NSLog(@"创建失败");
    }
}

- (SelectType)colIsInTable:(MZY_PictureModel *)pic{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from collect where photo_id = '%@' and flag = '1' ",pic.photo_id];
    // 跟随指针
    sqlite3_stmt *stmt = nil;
    // 执行
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            sqlite3_finalize(stmt);
            return InTable;
        }else{
            sqlite3_finalize(stmt);
            return NotInTable;
        }
    }else{
        sqlite3_finalize(stmt);
        return SelectError;
    }
}



- (void)insertPic:(MZY_PictureModel *)pic{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into collect(photo_id,col_name,pic,pic_detail,col_count,comment_count,flag) values ('%@','%@','%@','%@','%@','%@','1')",pic.photo_id,pic.col_name,pic.pic,pic.pic_detail,pic.col_count,pic.comment_count];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"成功");
    }else{
        NSLog(@"失败");
    }
}

- (void)deletePic:(MZY_PictureModel *)pic{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from collect where photo_id = '%@'",pic.photo_id];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}

- (void)updatePic:(MZY_PictureModel *)pic{
    NSString *sqlStr = [NSString stringWithFormat:@"update collect set flag = '0' where photo_id= '%@' ",pic.photo_id];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }else{
        NSLog(@"更新失败");
    }
}

- (NSMutableArray *)selectAllPic{
    // "*"代表是通配符,代表了所有的字段名
    NSString *sqlStr = @"select * from Collect";
    // 需要创建一个跟随指针,用来遍历查询数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    NSMutableArray *arr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            MZY_PictureModel *picture = [[MZY_PictureModel alloc]init];
            // 把表里的值赋值给属性
            picture.number = number;
            
            // 表里取文本内容
            const unsigned char *name = sqlite3_column_text(stmt, 2);
            const unsigned char *pic = sqlite3_column_text(stmt, 1);
            const unsigned char *Id = sqlite3_column_text(stmt, 0);
            const unsigned char *Pic_detail = sqlite3_column_text(stmt, 3);
            const unsigned char *Col_count = sqlite3_column_text(stmt, 4);
            const unsigned char *Comment_count = sqlite3_column_text(stmt, 5);
            picture.col_name = [NSString stringWithUTF8String:(const char *)name];
            picture.pic = [NSString stringWithUTF8String:(const char *)pic];
            picture.photo_id = [NSString stringWithUTF8String:(const char *)Id];
            picture.pic_detail = [NSString stringWithUTF8String:(const char *)Pic_detail];
            picture.col_count = [NSString stringWithUTF8String:(const char *)Col_count];
            picture.comment_count = [NSString stringWithUTF8String:(const char *)Comment_count];
            [arr addObject:picture];
        }
    }
    // 注销跟随指针
    sqlite3_finalize(stmt);
    return arr;
}

@end
